/*
This script completes the following:
1.  Drops RowIdentifier and FileExtenstion from structure.ItemResource
2.  Drops RowIdentifier from structure.ItemVersion
3.  Sets default value of structure.ItemVersion.MimeType to 'application/octet-stream'
4.  Sets default value of structure.ItemVersion.LastModified to GetDate()
5.  Creates an Update Trigger to update structure.ItemVersion.LastModified to GetDate() on update.
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

USE PRODInventory

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO
ALTER TABLE structure.ItemResource
	DROP CONSTRAINT DF_ItemResource_RowId
GO
DROP INDEX ItemResourceRowIndex ON structure.ItemResource
GO
ALTER TABLE structure.ItemResource
	DROP COLUMN FileExtension, RowIdentifier
GO
ALTER TABLE structure.ItemResource SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

select Has_Perms_By_Name(N'structure.ItemResource', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'structure.ItemResource', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'structure.ItemResource', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION

GO
CREATE TABLE structure.Tmp_Item
	(
	Id uniqueidentifier NOT NULL,
	ParentId uniqueidentifier NULL,
	ExternalId varchar(100) NULL,
	PublishedVersion int NULL,
	ItemClaimId uniqueidentifier NULL,
	Href varchar(2000) NOT NULL
	)  ON [PRIMARY]

GO
IF EXISTS(SELECT * FROM structure.Item)
	 EXEC('INSERT INTO structure.Tmp_Item (Id, ParentId, ExternalId, PublishedVersion, ItemClaimId, Href)
		SELECT Id, ParentId, ExternalId, PublishedVersion, ItemClaimId, Href FROM structure.Item WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE structure.Item
	DROP CONSTRAINT FK_Item_Item
GO
ALTER TABLE structure.ItemEvent
	DROP CONSTRAINT FK_Event_Item
GO
ALTER TABLE structure.ItemVersion
	DROP CONSTRAINT FK_ItemVersion_Item
GO
DROP TABLE structure.Item
GO
EXECUTE sp_rename N'structure.Tmp_Item', N'Item', 'OBJECT' 
GO
ALTER TABLE structure.Item ADD CONSTRAINT
	PK_Item PRIMARY KEY CLUSTERED 
	(
	Id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX NonClusteredIndex ON structure.Item
	(
	Id,
	ParentId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE structure.Item ADD CONSTRAINT
	FK_Item_Item FOREIGN KEY
	(
	ParentId
	) REFERENCES structure.Item
	(
	Id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
DECLARE @v sql_variant 
SET @v = N'Parent child heirarchy'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'structure', N'TABLE', N'Item', N'CONSTRAINT', N'FK_Item_Item'
GO

select Has_Perms_By_Name(N'structure.Item', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'structure.Item', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'structure.Item', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE structure.ItemVersion ADD CONSTRAINT
	DF_ItemVersion_MimeType DEFAULT 'application/octet-stream' FOR MimeType
GO
ALTER TABLE structure.ItemVersion ADD CONSTRAINT
	DF_ItemVersion_LastModified DEFAULT GetDate() FOR LastModified
GO
DROP INDEX ItemVersionRowIndex ON structure.ItemVersion
GO
ALTER TABLE structure.ItemVersion ADD CONSTRAINT
	FK_ItemVersion_Item FOREIGN KEY
	(
	ItemId
	) REFERENCES structure.Item
	(
	Id
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
ALTER TABLE structure.ItemVersion
	DROP COLUMN RowIdentifier
GO
ALTER TABLE structure.ItemVersion SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'structure.ItemVersion', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'structure.ItemVersion', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'structure.ItemVersion', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE structure.ItemEvent ADD CONSTRAINT
	FK_Event_Item FOREIGN KEY
	(
	ItemId
	) REFERENCES structure.Item
	(
	Id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE structure.ItemEvent SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'structure.ItemEvent', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'structure.ItemEvent', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'structure.ItemEvent', 'Object', 'CONTROL') as Contr_Per 
